﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using System.Data;

namespace MSAS.FrontEnd.DL
{
    /// <summary>
    /// Encapsulate retrieval helper functions for the Delivery Service
    /// </summary>
    public class DataRetrieve
    {
        private const string SqlLookupQuery = "SELECT ReputationBinary "
            + "FROM WebService.Object (nolock) "
            + "WHERE ObjectTypeId = @p0 "
            + "  AND SHA256 IN ({0})";

        private const string SqlParam = "@p{0}";

        /// <summary>
        /// Use ADO.NET to retrieve a list of objects by the identifiers (sha256) of a specific type from the database.
        /// This method construct the ADO SQLCmd to execute it directly against the database since all other methods 
        /// does not perform as efficiently.
        /// </summary>
        /// <param name="objectType">the int representation of the object type (see ObjectTypeEnum)</param>
        /// <param name="sha256s">List of sha256 to retrieve</param>
        /// <returns></returns>
        public static List<byte[]> LookupObjects(short objectType, List<byte[]> sha256s)
        {
            List<byte[]> result = new List<byte[]>();

            string lookupCommand = BuildLookupCommand(sha256s.Count);

            using (SqlConnection sqlConnection = new SqlConnection(Properties.Settings.Default.PSDB_ConnectionString))
            {
                using (SqlCommand sqlCmd = new SqlCommand(lookupCommand, sqlConnection))
                {
                    SetLookupParameters(sqlCmd, objectType, sha256s);

                    try
                    {
                        sqlConnection.Open();

                        using (SqlDataReader reader = sqlCmd.ExecuteReader())
                        {
                            while (reader.Read())
                            {
                                result.Add(reader.GetSqlBytes(0).Value);
                            }
                        }
                    }
                    catch (Exception ex)
                    {
                        throw new ApplicationException("Error when executing Lookup operation against the database", ex);
                    }
                    finally
                    {
                        // force closing even in case of error
                        if (sqlConnection.State != ConnectionState.Closed)
                        {
                            sqlConnection.Close();
                        }
                    }
                }
            }

            return result;
        }

        /// <summary>
        /// Retrieve the list of overrides since the last check
        /// </summary>
        /// <param name="lastStamp"></param>
        /// <returns></returns>
        public static List<prc_GetOverridesResult> GetOverrides(int? lastStamp)
        {
            using (ServiceDataContext db = new ServiceDataContext())
            {
                return db.prc_GetOverrides(lastStamp).ToList<prc_GetOverridesResult>();
            }
        }

        /// <summary>
        /// Build the Lookup command by inserting in the SQLLookupQuery the proper
        /// number of parameters for each sha256 that we are seeking
        /// </summary>
        /// <param name="parameterCount">number of sha to search for</param>
        /// <returns>sql command string ready to accept our parameters</returns>
        private static string BuildLookupCommand(int parameterCount)
        {
            StringBuilder sb = new StringBuilder();
            for (int i = 0; i < parameterCount; i++)
            {
                if (i != 0)
                {
                    sb.Append(", ");
                }

                // we add one since param 0 is the objecttype
                sb.AppendFormat(SqlParam, i + 1);
            }

            return string.Format(SqlLookupQuery, sb.ToString());
        }

        /// <summary>
        /// Populate the parameters of the sql command
        /// </summary>
        /// <param name="cmd">created sql command</param>
        /// <param name="objectType">object type</param>
        /// <param name="sha256s">list of sha256</param>
        private static void SetLookupParameters(SqlCommand cmd, short objectType, List<byte[]> sha256s)
        {
            // add the objectType
            cmd.Parameters.Add(new SqlParameter
            {
                ParameterName = string.Format(SqlParam, 0),
                SqlDbType = SqlDbType.Int,
                Value = objectType
            });

            int i = 1;
            cmd.Parameters.AddRange( 
                (
                    from sha in sha256s
                    select new SqlParameter 
                    {
                        ParameterName = string.Format(SqlParam, i++),
                        SqlDbType = SqlDbType.Binary,
                        Size = 32,
                        Value = sha
                    } ).ToArray<SqlParameter>() 
                );
        }
    }
}
